package monster.comm.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import sun.jdbc.rowset.CachedRowSet;

import monster.comm.exception.ServiceException;
import monster.comm.log.ILog;
import monster.comm.log.LogService;
import monster.comm.util.SpringApplicationContextFactory;

/**
 * 
 * @author 这是一个对jdbc的封装类，所有的jdbc请尽量使用此类
 * 
 */
public class JdbcTemplateImp implements JdbcTemplatService {
	protected JdbcTemplate jdbcTemplate;

	private static String datasourcename = "dataSourceClient";

	private static ILog log = LogService.getLog(JdbcTemplateImp.class);

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * 这个方法不能保持组合事务，在DAO不能使用这个方法，应该用依赖注入
	 * 
	 * @return
	 * @throws Exception
	 */
	public JdbcTemplate getTemplate() throws ServiceException {
		if (jdbcTemplate == null) {
			try{
			   DataSource ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
			
			   jdbcTemplate = new JdbcTemplate(ds);
			}catch(Exception e){
				throw 	new ServiceException(e);		
			}
		}

		return jdbcTemplate;
	}

	/**
	 * 得到数据库连接池
	 * 
	 * @return
	 * @throws Exception
	 */
	public DataSource getDS() throws ServiceException {
		DataSource ds=null;
		try {
			ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
		} catch (Exception e) {
			throw new ServiceException(e);
		}

		return ds;
	}

	/**
	 * 执行指定的增、删、改SQL语句
	 * 
	 * @param sql
	 *            增、删、改SQL语句
	 */
	public void update(String sql) throws ServiceException {
		if (jdbcTemplate == null){
			log.error("jdbcTemplate为null");
			throw new ServiceException("jdbcTemplate为null");
		}
			
		try {
			log.info("run  " + sql);
			jdbcTemplate.update(sql);

		} catch (Exception e) {
			log.error("jdbcTemplate执行sql例外" + sql,e);
			throw new ServiceException("jdbcTemplate执行sql例外" + sql + "--" + e);
		}
	}

	/**
	 * 执行指定的增、删、改SQL准备语句
	 * 
	 * @param sql
	 *            增、删、改SQL准备语句
	 * @param params
	 *            参数
	 */
	public void updateByParam(String sql, Object[] params)
			throws ServiceException {
		if (jdbcTemplate == null){
			log.error( "jdbcTemplate为null");
			throw new ServiceException("jdbcTemplate为null");
		}
		try {
			log.info("run  " + sql);
			jdbcTemplate.update(sql, params);
		} catch (Exception e) {
			log.error( "jdbcTemplate执行sql例外" + sql,e);
			throw new ServiceException("jdbcTemplate执行sql例外" + sql + "--" + e);
		}
	}

	/**
	 * 批修改删除
	 * 
	 * @param sql[]
	 *            sql数组
	 * @return 每个数组的值是对应的sql修改数
	 * @throws ServiceException
	 */
	public int[] batchMoreUpdate(String[] sql) throws ServiceException {
		try {
			return jdbcTemplate.batchUpdate(sql);
		} catch (Exception e) {
			log.error( "jdbcTemplate执行sql例外" + sql,e);
			throw new ServiceException("jdbcTemplate执行sql例外" + sql + "--" + e);
		}
	}

	/**
	 * 信息引用的存储过程调用
	 * 
	 * @param nt_news_id,quote_user_id,des_nt_id
	 *  传递参数顺序为4个入参，1个出参
	 * @return 提示信息
	 */
	public String call(String nt_news_id, String quote_user_id, String des_nt_id) {
		String returnstr = "";
		try {
			Connection conn = jdbcTemplate.getDataSource().getConnection();
			CallableStatement callStmt = conn
					.prepareCall("{call JXGC_MH.NEWSQUOTE (?,?,?,?)}");
			callStmt.setInt(1, Integer.parseInt(nt_news_id));
			callStmt.setInt(2, Integer.parseInt(quote_user_id));
			callStmt.setInt(3, Integer.parseInt(des_nt_id));
			callStmt.registerOutParameter(4, java.sql.Types.VARCHAR);
			callStmt.executeUpdate();
			returnstr = callStmt.getString(4);
		} catch (SQLException e) {
			log.error( e.getMessage(),e);
		}
		return returnstr;
	}
	
	public String call(String callname,String [] param){
		String returnstr = "";
		try {
			Connection conn = jdbcTemplate.getDataSource().getConnection();
			String sql="{call JXGC_MH."+callname+" (";
			for(int i=0;i<param.length;i++){
				if(param.length-i==1){
					sql+="?)}";
				}else{
					sql+="?,";
				}
			}
			CallableStatement callStmt = conn
					.prepareCall(sql);
			
			for(int i=0;i<param.length;i++){
				if(param.length-i==1){
					callStmt.registerOutParameter(i+1, java.sql.Types.VARCHAR);
				}else{
					callStmt.setObject(i+1, param[i]);
				}
				
			}
			callStmt.executeUpdate();
			returnstr = callStmt.getString(param.length);
		} catch (SQLException e) {
			log.error( e.getMessage(),e);
		}
		return returnstr;
	}

	/**
	 * 从一条sql语句里面获得一个vo对象的list集合
	 * 
	 * @param sql
	 *            eg:select * from test
	 * @param voClass
	 *            对应vo的class类,
	 * @param dataField
	 *            这个要注意，就是从ResulSet里面取出字段填充进vo的属性值，里面数组的大小写和属vo属性名的大小写要保持一致,如果vo的属性名集合<=resultSet包含的字段
	 *            则把这个dataFields设置为null
	 * @return vo对象的list集合
	 * @throws ServiceException
	 */
	public List queryListBySql(String sql, Class voClass, String[] dataFields)
			throws ServiceException {
		List list = new ArrayList();
		try {
			Object vo = voClass.newInstance();
			if (dataFields == null || dataFields.length == 0) {
				dataFields = getBeanProperty(vo);
			}
			log.info("run  " + sql);
			jdbcTemplate.query(sql, new MyRowCall(voClass, dataFields, list));
		} catch (Exception e) {
			log.error( e.getMessage(),e);
			throw new ServiceException(e);
		}

		return list;
	}

	/**
	 * 从一条sql语句里面获得一个vo对象的list集合,前提：vo的属性名集合<=resultSet包含的字段
	 * 
	 * @param sql
	 *            eg:select * from test
	 * @param voClass
	 *            对应vo的class类,
	 * @return vo对象的list集合
	 * @throws ServiceException
	 */
	public List queryListBySql(String sql, Class voClass)
			throws ServiceException {
		return queryListBySql(sql, voClass, null);
	}

	/**
	 * 从一条sql语句里面获得一个vo对象 参数说明请参考queryListBySql()方法
	 * 
	 * @param sql
	 * @param voClass
	 * @param dataFields
	 * @return
	 * @throws ServiceException
	 */
	public Object findVoBySql(String sql, Class voClass, String[] dataFields)
			throws ServiceException {
		List list = queryListBySql(sql, voClass, dataFields);
		return list.get(0);
	}

	/**
	 * 从一条sql语句里面获得一个vo对象,前提：vo的属性名集合<=resultSet包含的字段 参数说明请参考queryListBySql()方法
	 * 
	 * @param sql
	 * @param voClass
	 * @return
	 * @throws ServiceException
	 */
	public Object findVoBySql(String sql, Class voClass)
			throws ServiceException {
		List list = queryListBySql(sql, voClass);
		return list.get(0);
	}

	/**
	 * 得到指定查询语句的记录集
	 * 
	 * @param sql
	 *            普通查询语句
	 * @return CachedRowSet
	 */
	public CachedRowSet findRowSet(String sql) throws ServiceException {

		CachedRowSet crs = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			log.info("run  " + sql);
			DataSource ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
			con = ds.getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			crs = new CachedRowSet();
			crs.populate(rs);

		} catch (Exception e) {
			log.error( e.getMessage(),e);
			throw new ServiceException(e.toString());
		} finally {
			try {
				closeResultSet(rs);
				closeStatement(ps);
				closeConnection(con);
			} catch (ServiceException e) {
				log.error(e.getMessage(),e);
				throw new ServiceException(e);
			}

		}

		return crs;

	}

	/**
	 * 得到指定准备语句的查询记录集
	 * 
	 * @param sql
	 *            查询准备语句
	 * @param arg
	 *            条件参数
	 * @return CachedRowSet
	 */
	public CachedRowSet findRowSetByParam(String sql, Hashtable arg)
			throws ServiceException {
		CachedRowSet crs = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		try {

			DataSource ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
			con = ds.getConnection();
			log.info("run  " + sql);
			ps = con.prepareStatement(sql);
			int k = 0;
			if (arg != null) {
				int len = arg.size();
				if (len > 0) {
					for (k = 0; k < len; k++) {
						ps.setObject(k + 1, arg.get(String.valueOf(k)));
					}
				}
			}

			rs = ps.executeQuery();
			crs = new CachedRowSet();
			crs.populate(rs);
		} catch (Exception e) {
			log.error( e.getMessage(),e);
			throw new ServiceException(e.toString());
		} finally {
			try {
				closeResultSet(rs);
				closeStatement(ps);
				closeConnection(con);
			} catch (ServiceException e) {
				log.error(e.getMessage(),e);
				throw new ServiceException(e);
			}

		}

		return crs;

	}

	/**
	 * 得到指定准备语句的查询记录集总条数
	 * 
	 * @param sql
	 *            查询准备语句
	 * @param arg
	 *            条件参数
	 * @return int 记录总条数
	 */

	public int findRowCount(String sql, Hashtable arg) throws  ServiceException{
		int count = 0;
		CachedRowSet crs = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			DataSource ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
			con = ds.getConnection();
			ps = con.prepareStatement(sql);
			log.info("run total num:" + sql);
			int k = 0;
			if (arg != null) {
				int len = arg.size();
				if (len > 0) {
					for (k = 0; k < len; k++) {
						ps.setObject(k + 1, arg.get(String.valueOf(k)));
					}
				}
			}

			rs = ps.executeQuery();
			crs = new CachedRowSet();
			crs.populate(rs);
			while (crs.next()) {
				count = crs.getInt(1);
			}
		} catch (Exception e) {
			log.error( e.getMessage(),e);
			throw new ServiceException(e);
		} finally {
			try {
				closeResultSet(rs);
				closeStatement(ps);
				closeConnection(con);
			} catch (ServiceException e) {
				log.error(e.getMessage(),e);
				throw new ServiceException(e);
			}

		}

		return count;
	}

	/**
	 * 得到指定页的记录,用于分页
	 * 
	 * @param sql
	 *            查询的准备语句
	 * @param arg
	 *            查询的参数对象集合
	 * @param rowscount
	 *            查询语句查询出的总记录数
	 * @param pageIndex
	 *            第几页
	 * @param pageSize
	 *            每页显示条数
	 * @return CachedRowSet
	 */

	public CachedRowSet findRowset(String sql, Hashtable arg, int rowscount,
			int pageIndex, int pageSize) throws ServiceException{

		int rowstart = 0;
		int rowend = 0;
		if (rowscount > 0) {
			rowstart = (pageIndex - 1) * pageSize;
			rowend = pageIndex * pageSize;
			if (rowend > rowscount)
				rowend = rowscount;
		}

		CachedRowSet crs = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			DataSource ds = (DataSource) SpringApplicationContextFactory
					.getService(datasourcename);
			con = ds.getConnection();
			log.info("run  " + sql);
			ps = con.prepareStatement(sql);
			int k = 0;
			if (arg != null) {
				int len = arg.size();
				if (len > 0) {
					for (k = 0; k < len; k++) {
						System.out.println("=ddd====="
								+ arg.get(String.valueOf(k)));
						ps.setObject(k + 1, arg.get(String.valueOf(k)));
					}
				}
			}
			ps.setInt(++k, rowend);
			ps.setInt(++k, rowstart);

			rs = ps.executeQuery();
			crs = new CachedRowSet();
			crs.populate(rs);
		} catch (Exception e) {
			log.error( e.getMessage(),e);
			throw new ServiceException(e.toString());
		} finally {
			try {
				closeResultSet(rs);
				closeStatement(ps);
				closeConnection(con);
			} catch (ServiceException e) {
				log.error(e.getMessage(),e);
				throw new ServiceException(e);
			}

		}

		return crs;
	}

	/**
	 * 获得一个bean的属性名集合
	 * 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	private String[] getBeanProperty(Object bean) throws Exception {
		String sz[];
		java.beans.BeanInfo info = java.beans.Introspector.getBeanInfo(bean
				.getClass());
		java.beans.PropertyDescriptor[] pds = info.getPropertyDescriptors();
		sz = new String[pds.length - 1];
		int j = 0;
		for (int i = 0; i < pds.length; i++) {
			String proName = pds[i].getName();
			if (!"class".equals(proName)) {
				sz[j] = proName;
				j++;
			}
		}
		return sz;
	}

	/**
	 * 执行函数并返回结果集
	 * example
	 * String sql =
				"begin :1 := fs_list_jsshgyxx("
					+ (String) f.get("shb_id")
					+ "); end;";
			CachedRowSet rs = mydb.exec(sql);
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public CachedRowSet exec(String sql) throws ServiceException {
		int num=0;
		Connection conn =null;
		
		
		//System.out.println(sql);
		CallableStatement cstmt = null;
		CachedRowSet cst=null;
		ResultSet rs = null;
		try {
			cst = new CachedRowSet();
			conn= jdbcTemplate.getDataSource().getConnection();
			//sql = escapeNull(sql);
			cstmt = conn.prepareCall(sql);
			cstmt.registerOutParameter(1, java.sql.Types.OTHER);
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);
			cst.populate(rs);
			num++;
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			throw new ServiceException("数据查询出错" + e.getMessage() + "出错sql为：" + sql);
		} finally {
			try {
				closeResultSet(rs);
				closeStatement(cstmt);
				closeConnection(conn);
			} catch (ServiceException e) {
				log.error(e.getMessage(),e);
				throw new ServiceException(e);
			}
			
		}
		return cst;
	}
	private static void closeResultSet(ResultSet rs)throws ServiceException{
		if(rs!=null)
			try {
				rs.close();
			} catch (SQLException e) {
				throw new ServiceException(e);
			}
	}
	private static void closeStatement(Statement st)throws ServiceException{
		if(st!=null)
			try {
				st.close();
			} catch (SQLException e) {
				throw new ServiceException(e);
			}
	}
	private static void closeConnection(Connection con)throws ServiceException{
		if(con!=null)
			try {
				con.close();
			} catch (SQLException e) {
				throw new ServiceException(e);
			}
	}
	
}

class MyRowCall implements RowCallbackHandler {
	Class voClass;

	String[] dataFields;

	List list;

	public MyRowCall(Class voClass, String[] dataFields, List list) {
		this.voClass = voClass;
		this.dataFields = dataFields;
		this.list = list;
	}

	public void processRow(ResultSet rs) throws SQLException {
		try {
			Object vo = voClass.newInstance();

			BeanWrapper bw = new BeanWrapperImpl(vo);
			for (int i = 0; i < dataFields.length; i++) {
				bw.setPropertyValue(dataFields[i], rs.getString(dataFields[i]));
			}
			list.add(vo);
		} catch (Exception e) {
			throw new SQLException();
		}

	}

}
